package DataAccess;

import DomainModel.*;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author Reynier
 * @date 11/10/2013
 * 
 */
public class ClassWorkDA 
{
    private DBManager dbm;
    private Connection conn;
    private PreparedStatement prep;
    
    public ClassWorkDA() throws Exception {
        dbm = DBManager.getInstance();
        conn = dbm.getConnection();
    }
    
    public List<ClassWorkType> getClassWorkTypess() throws Exception {
        List<ClassWorkType> classWorkTypes = new LinkedList<ClassWorkType>();
       
        String query = "select id, name from class_work_types order by name";
        prep = conn.prepareStatement(query);
        
        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                classWorkTypes.add(new ClassWorkType(rs.getInt("id"), rs.getString("name")));
            }
        }
        catch (SQLException e) {
            throw e;
        }
        finally {
            prep.close();
        }
        
        return classWorkTypes;
    }
    
    public List<ClassWork> getClassWorks(int professorId, int sectionId) throws Exception {
        List<ClassWork> classWorks = new LinkedList<ClassWork>();
       
        String query = "select cw.class_work_type_id, cwt.name as class_work_type_name, cw.id as class_work_id, cw.assigned_date, \n" +
                "	cw.due_date, cw.max_points, cw.name as class_work_name, cw.weight, cw.section_id \n" +
                "from class_works cw \n" +
                "join class_work_types cwt on cwt.id = cw.class_work_type_id \n" +
                "join sections s on s.id = cw.section_id \n" +
                "where s.professor_id = ? and cw.section_id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, professorId);
        prep.setInt(2, sectionId);
        
        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                classWorks.add(this.createClassWork(rs));
            }
        }
        catch (SQLException e) {
            throw e;
        }
        finally {
            prep.close();
        }
        
        return classWorks;
    }
    
    public boolean deleteClassWork(int professorId, int classWorkId) throws Exception {
        ClassWork classWork = null;
       
        String query = "delete cw \n" +
                "from class_works cw \n" +
                "join class_work_types cwt on cwt.id = cw.class_work_type_id \n" +
                "join sections s on s.id = cw.section_id \n" +
                "where s.professor_id = ? and cw.id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, professorId);
        prep.setInt(2, classWorkId);
        
        try {
            prep.executeUpdate();
            classWork = getClassWorkById(professorId, classWorkId);
        }
        catch (SQLException e) {
            throw e;
        }
        finally {
            prep.close();
        }
        
        return (classWork == null);
    }
    
    public ClassWork getClassWorkById(int professorId, int classWorkId) throws Exception {
        ClassWork classWork = null;
       
        String query = "select cw.class_work_type_id, cwt.name as class_work_type_name, cw.id as class_work_id, cw.assigned_date, \n" +
                "	cw.due_date, cw.max_points, cw.name as class_work_name, cw.weight, cw.section_id \n" +
                "from class_works cw \n" +
                "join class_work_types cwt on cwt.id = cw.class_work_type_id \n" +
                "join sections s on s.id = cw.section_id \n" +
                "where s.professor_id = ? and cw.id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, professorId);
        prep.setInt(2, classWorkId);
        
        try (ResultSet rs = prep.executeQuery()) {
            if (rs.next()) {
                classWork = createClassWork(rs);
            }
        }
        catch (SQLException e) {
            throw e;
        }
        finally {
            prep.close();
        }
        
        return classWork;
    }
    
    
     public ClassWork getClassWorkById(int classWorkId) throws Exception {
        ClassWork classWork = null;
       
        String query = "select cw.class_work_type_id, cwt.name as class_work_type_name, cw.id as class_work_id, cw.assigned_date, \n" +
                "	cw.due_date, cw.max_points, cw.name as class_work_name, cw.weight, cw.section_id \n" +
                "from class_works cw \n" +
                "join class_work_types cwt on cwt.id = cw.class_work_type_id \n" +
                "join sections s on s.id = cw.section_id \n" +
                "where cw.id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, classWorkId);
        
        try (ResultSet rs = prep.executeQuery()) {
            if (rs.next()) {
                classWork = createClassWork(rs);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            prep.close();
        }
        
        return classWork;
    }
    
    
    
    public void setClassWork(ClassWork classWork) throws Exception {
        String q;
        
        if (classWork.getClassWorkId()== 0) {
            // INSERT new class work
            q = "INSERT INTO class_works (name, assigned_date, due_date, max_points, weight, section_id, class_work_type_id) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
            prep = conn.prepareStatement(q);
            prep.setString(1, classWork.getName());
            prep.setString(2, classWork.getAssignedDate());
            prep.setString(3, classWork.getDueDate());
            prep.setInt(4, classWork.getMaxPoints());
            prep.setDouble(5, classWork.getWeight());
            prep.setInt(6, classWork.getSectionId());
            prep.setInt(7, classWork.getClassWorkType().getClassWorkTypeId());
        }
        else {
            // UPDATE class work
             q = "UPDATE class_works " +
                 "SET name = ?, due_date = ?, max_points = ?, weight = ?, class_work_type_id = ? " +
                 "WHERE id = ?";
            prep = conn.prepareStatement(q);
            prep.setString(1, classWork.getName());
            prep.setString(2, classWork.getDueDate());
            prep.setInt(3, classWork.getMaxPoints());
            prep.setDouble(4, classWork.getWeight());
            prep.setInt(5, classWork.getClassWorkType().getClassWorkTypeId());
            prep.setDouble(6, classWork.getClassWorkId());
        }
        
        try  {
            prep.executeUpdate();
        }
        catch (SQLException e) {
            throw e;
        }
        finally {
            prep.close();
        }
    }
    
    private ClassWork createClassWork(ResultSet rs) throws Exception {
        if (rs == null) return null;
        
        LetterGrade letterGrade;
        ClassWorkType classWorkType;
        ClassWork classWork;
                        
        // Class Work Type
        int classWorkTypeId = rs.getInt("class_work_type_id");
        String classWorkTypeName = rs.getString("class_work_type_name");
        classWorkType = new ClassWorkType(classWorkTypeId, classWorkTypeName);
        
        // Class Work
        int classWorkId = rs.getInt("class_work_id");
        String assignedDate = rs.getString("assigned_date");
        String dueDate = rs.getString("due_date");
        int maxPoints = rs.getInt("max_points");
        String classWorkName = rs.getString("class_work_name");
        int weight = rs.getInt("weight");
        int sectionId = rs.getInt("section_id");
        
        return new ClassWork(classWorkId, classWorkType, sectionId, classWorkName, maxPoints, assignedDate, dueDate, weight);
    }
}
